//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "CStatistique.h"
#include "dm_Kiloutou.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
//---------------------------------------------------------------------------
__fastcall TvAStatistiques::TvAStatistiques(TComponent* Owner)
    : TForm(Owner)
{
    FenetreNormale();
}
//---------------------------------------------------------------------------
//          AFFICHAGE DE LA FENETRE SANS LA GRILLE
//---------------------------------------------------------------------------
void __fastcall TvAStatistiques::FenetreNormale()
{
    sgResultat->Visible  = false;
    this->Width = 207;
    this->Height = 317;

    bMoyenneLoc->Left = 32;
    bMoyenneLoc->Top  = 8;

    bNombreLoc->Left = 32;
    bNombreLoc->Top = 40;

    bNombreCamion->Left = 32;
    bNombreCamion->Top = 72;

    bNombreTondeuse->Left = 32;
    bNombreTondeuse->Top = 104;

    bNombrePelleteuse->Left = 32;
    bNombrePelleteuse->Top = 136;

    bCamionPoidsTotal->Left = 32;
    bCamionPoidsTotal->Top = 168;

    bVitesseTondaison->Left = 32;
    bVitesseTondaison->Top = 200;

    bPelleteusePoidsTotal->Left = 32;
    bPelleteusePoidsTotal->Top = 232;
}
//---------------------------------------------------------------------------
//          AFFICHAGE DE LA FENETRE AVEC LA GRILLE
//---------------------------------------------------------------------------
void __fastcall TvAStatistiques::FenetreGrid()
{
    sgResultat->Visible  = true;
    this->Width = 476;
    this->Height = 302;

    bMoyenneLoc->Left = 336;
    bMoyenneLoc->Top  = 8;

    bNombreLoc->Left = 336;
    bNombreLoc->Top = 40;

    bNombreCamion->Left = 336;
    bNombreCamion->Top = 72;

    bNombreTondeuse->Left = 336;
    bNombreTondeuse->Top = 104;

    bNombrePelleteuse->Left = 336;
    bNombrePelleteuse->Top = 136;

    bCamionPoidsTotal->Left = 336;
    bCamionPoidsTotal->Top = 168;

    bVitesseTondaison->Left = 336;
    bVitesseTondaison->Top = 200;

    bPelleteusePoidsTotal->Left = 336;
    bPelleteusePoidsTotal->Top = 232;
}
//---------------------------------------------------------------------------
//              MOYENNE D UNE LOCATION
//---------------------------------------------------------------------------

void __fastcall TvAStatistiques::bMoyenneLocClick(TObject *Sender)
{
    FenetreGrid();
    TIBQuery * MaRequete = NULL;
    MaRequete = new TIBQuery((TComponent*)0);
    MaRequete->Database = dmKiloutou->dbKiloutou;

    MaRequete->SQL->Text =  "SELECT AVG(CAST(location.loc_date_heure_retour - location.loc_date_heure_debut AS integer)) "
                            "FROM location "
                            "where location.loc_date_heure_retour != '12/30/1899 12:00\'";

    MaRequete->Open();
    MaRequete->First();

    //Affichage dans la TStringGrid
    //-----------------------------
    sgResultat->Visible             = true;
    sgResultat->ColCount            = 1;
    sgResultat->RowCount            = 2;
    sgResultat->Color               = clActiveCaption;
    sgResultat->DefaultColWidth     = 240;
    sgResultat->DefaultRowHeight    = 30;
    sgResultat->Cols[0]->Text       = "Moyenne d'une location en jours";
    sgResultat->Cells[0][1]         = MaRequete->Fields->Fields[0]->AsInteger;

    MaRequete->SQL->Clear();
    MaRequete->Close();
    if(MaRequete) {delete MaRequete; MaRequete = NULL;}

}
//---------------------------------------------------------------------------
//              NOMBRE DE LOCATIONS PAR PERSONNE
//---------------------------------------------------------------------------
void __fastcall TvAStatistiques::bNombreLocClick(TObject *Sender)
{
    FenetreGrid();
    TIBQuery * MaRequete = NULL;
    MaRequete = new TIBQuery((TComponent*)0);
    MaRequete->Database = dmKiloutou->dbKiloutou;

    MaRequete->SQL->Text =  "select     A.nom \"nom\",A.prenom \"prénom\","
                                        "count(B.loc_id_client) \"nombre de locations\" "
                            "from client A "
                                "left outer join location B on (A.code_client = B.loc_id_client) "
                            "group by A.nom, A.prenom "
                            "order by upper(A.nom), upper(A.prenom)";
    MaRequete->Open();
    MaRequete->First();

    sgResultat->Visible  = true;
    sgResultat->Cols[0]->Text   =  MaRequete->Fields->Fields[0]->FieldName;
    sgResultat->Cols[1]->Text   =  MaRequete->Fields->Fields[1]->FieldName;
    sgResultat->Cols[2]->Text   =  MaRequete->Fields->Fields[2]->FieldName;
    sgResultat->ColCount = 3;
    sgResultat->DefaultColWidth = 100;

    int Ligne = 1;
    while(!MaRequete->Eof)
    {
        sgResultat->Cells[0][Ligne] =  MaRequete->Fields->Fields[0]->AsString;
        sgResultat->Cells[1][Ligne] =  MaRequete->Fields->Fields[1]->AsString;
        sgResultat->Cells[2][Ligne] =  MaRequete->Fields->Fields[2]->AsString;
        Ligne++;
        MaRequete->Next();
    }
    sgResultat->RowCount =  Ligne;
    MaRequete->SQL->Clear();
    MaRequete->Close();
    if(MaRequete) {delete MaRequete; MaRequete = NULL;}
}
//---------------------------------------------------------------------------
//              NOMBRE DE LOCATIONS PAR TYPE DE MATERIEL
//---------------------------------------------------------------------------
void __fastcall TvAStatistiques::NombreLocationMateriel(TTypeMateriel Materiel)
{
    FenetreGrid();
    TIBQuery * MaRequete = NULL;
    MaRequete = new TIBQuery((TComponent*)0);
    MaRequete->Database = dmKiloutou->dbKiloutou;

    MaRequete->SQL->Text =  (AnsiString)"select  A.nom \"nom\","+
                                        "count(B.loc_id_client) \"nombre de locations\" "+
                                        "from materiel A "+
                                        "left outer join location B "+
                                        "on (A.code_materiel = B.loc_id_materiel) " +
                                        "where A.type_materiel ="+Materiel+
                                        "group by A.nom";
    MaRequete->Open();
    MaRequete->First();

    sgResultat->Visible  = true;
    sgResultat->Cols[0]->Text   =  MaRequete->Fields->Fields[0]->FieldName;
    sgResultat->Cols[1]->Text   =  MaRequete->Fields->Fields[1]->FieldName;
    sgResultat->ColCount = 2;
    sgResultat->DefaultColWidth = 100;

    int Ligne = 1;
    while(!MaRequete->Eof)
    {
        sgResultat->Cells[0][Ligne] =  MaRequete->Fields->Fields[0]->AsString;
        sgResultat->Cells[1][Ligne] =  MaRequete->Fields->Fields[1]->AsString;
        Ligne++;
        MaRequete->Next();
    }
    sgResultat->RowCount =  Ligne;
    MaRequete->SQL->Clear();
    MaRequete->Close();
    if(MaRequete) {delete MaRequete; MaRequete = NULL;}
}
//Camion
//------
void __fastcall TvAStatistiques::bNombreCamionClick(TObject *Sender)
{
    NombreLocationMateriel(TYPE_Camion);
}

//Tondeuse
//--------
void __fastcall TvAStatistiques::bNombreTondeuseClick(TObject *Sender)
{
    NombreLocationMateriel(TYPE_Tondeuse);
}

//Pelleteuse
//----------
void __fastcall TvAStatistiques::bNombrePelleteuseClick(TObject *Sender)
{
    NombreLocationMateriel(TYPE_Pelleteuse);
}
//---------------------------------------------------------------------------
//              POIDS TOTAL DEPLACABLE DES CAMIONS
//---------------------------------------------------------------------------
void __fastcall TvAStatistiques::bCamionPoidsTotalClick(TObject *Sender)
{
    FenetreGrid();
    TIBQuery * MaRequete = NULL;
    MaRequete = new TIBQuery((TComponent*)0);
    MaRequete->Database = dmKiloutou->dbKiloutou;

    MaRequete->SQL->Text =  "select  sum(A.poidstotal) \"Poids total des camions en kg\" from camion A";
    MaRequete->Open();
    MaRequete->First();

    sgResultat->Visible  = true;
    sgResultat->Cols[0]->Text   =  MaRequete->Fields->Fields[0]->FieldName;
    sgResultat->ColCount = 1;
    sgResultat->DefaultColWidth = 240;
    sgResultat->Cells[0][1] =  MaRequete->Fields->Fields[0]->AsInteger;
    MaRequete->SQL->Clear();
    MaRequete->Close();
    if(MaRequete) {delete MaRequete; MaRequete = NULL;}
}

//---------------------------------------------------------------------------
//              POIDS TOTAL DEPLACABLE DES PELLETEUSES
//---------------------------------------------------------------------------

void __fastcall TvAStatistiques::bPelleteusePoidsTotalClick(TObject *Sender)
{
    FenetreGrid();
    TIBQuery * MaRequete = NULL;
    MaRequete = new TIBQuery((TComponent*)0);
    MaRequete->Database = dmKiloutou->dbKiloutou;

    MaRequete->SQL->Text =  "select  sum(A.poidstotal) \"Poids total des pelleteuses en kg \" from pelleteuse A";
    MaRequete->Open();
    MaRequete->First();

    sgResultat->Visible  = true;
    sgResultat->Cols[0]->Text   =  MaRequete->Fields->Fields[0]->FieldName;
    sgResultat->ColCount = 1;
    sgResultat->DefaultColWidth = 240;
    sgResultat->Cells[0][1] =  MaRequete->Fields->Fields[0]->AsInteger;
    MaRequete->SQL->Clear();
    MaRequete->Close();
    if(MaRequete) {delete MaRequete; MaRequete = NULL;}
}
//---------------------------------------------------------------------------

void __fastcall TvAStatistiques::bVitesseTondaisonClick(TObject *Sender)
{
    FenetreGrid();
    TIBQuery * MaRequete = NULL;
    MaRequete = new TIBQuery((TComponent*)0);
    MaRequete->Database = dmKiloutou->dbKiloutou;

    MaRequete->SQL->Text =  "select  sum(A.vitessemax) \"Durée pour tondre 25400 m² \" from tondeuse A";
    MaRequete->Open();
    MaRequete->First();

    sgResultat->Visible  = true;
    sgResultat->Cols[0]->Text   =  MaRequete->Fields->Fields[0]->FieldName;
    sgResultat->ColCount = 1;
    sgResultat->DefaultColWidth = 240;
    if(MaRequete->Fields->Fields[0]->AsInteger != 0)
        sgResultat->Cells[0][1] =  (25400 / MaRequete->Fields->Fields[0]->AsInteger);
    MaRequete->SQL->Clear();
    MaRequete->Close();
    if(MaRequete) {delete MaRequete; MaRequete = NULL;}
}
//---------------------------------------------------------------------------

